--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_WRATE_DL_DATE_AMOUNT.HQL
--    Functions : 表47：存、贷款发生额加权平均利率时序统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_WRATE_DL_DATE_AMOUNT PARTITION (DATA_DATE = '20180331')
SELECT  AREA.AREA_CODE_4                                                            AS FIN_ORG_DIST     -- 金融机构地区
       ,CL.CCY                                                                      AS CCY              -- 贷款币种
       ,CONCAT(SUBSTR(CL.DATA_DATE,1,4),'年',SUBSTR(CL.DATA_DATE,5,2),'月')         AS DATE_SEQ         -- 时间序列
       ,SUM(CL.BAL_LOAN       )/100000000                                           AS BAL_LOAN         -- 贷款总余额
       ,SUM(CL.BAL_LOAN_SHORT )/100000000                                           AS BAL_LOAN_SHORT   -- 短期贷款余额
       ,SUM(CL.BAL_LOAN_LONG  )/100000000                                           AS BAL_LOAN_LONG    -- 中长期贷款余额
       ,SUM(CL.BAL_DEP        )/100000000                                           AS BAL_DEP          -- 存款总余额
       ,SUM(CL.BAL_DEP_CURR   )/100000000                                           AS BAL_DEP_CURR     -- 活期存款余额
       ,SUM(CL.BAL_DEP_FIXED  )/100000000                                           AS BAL_DEP_FIXED    -- 定期存款余额
       ,SUM(CL.WBAL_LOAN      )/100000000                                           AS WBAL_LOAN        -- 贷款余额加权值
       ,SUM(CL.WBAL_LOAN_SHORT)/100000000                                           AS WBAL_LOAN_SHORT  -- 短期贷款余额加权值
       ,SUM(CL.WBAL_LOAN_LONG )/100000000                                           AS WBAL_LOAN_LONG   -- 中长期贷款余额加权值
       ,SUM(CL.WBAL_DEP       )/100000000                                           AS WBAL_DEP         -- 存款总余额加权值
       ,SUM(CL.WBAL_DEP_CURR  )/100000000                                           AS WBAL_DEP_CURR    -- 活期存款余额加权值
       ,SUM(CL.WBAL_DEP_FIXED )/100000000                                           AS WBAL_DEP_FIXED   -- 定期存款余额加权值
FROM(
     SELECT FSE.DATA_DATE                                                                         AS DATA_DATE
           ,FSE.FIN_ORG_NO                                                                        AS FIN_ORG_NO
           ,FSE.CCY                                                                               AS CCY
           ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                          AS BAL_LOAN         -- 贷款总余额
           ,CASE WHEN TERM.LOAN_TERM_TYPE_ID='A' THEN COALESCE(FSE.OCCUR_AMOUNT,0) ELSE 0 END     AS AMOUNT_LOAN_SHORT   -- 短期贷款余额
           ,CASE WHEN TERM.LOAN_TERM_TYPE_ID='B' THEN COALESCE(FSE.OCCUR_AMOUNT,0) ELSE 0 END     AS AMOUNT_LOAN_LONG    -- 中长期贷款余额
           ,0                                                                                     AS AMOUNT_DEP          -- 存款总余额
           ,0                                                                                     AS AMOUNT_DEP_CURR     -- 活期存款余额
           ,0                                                                                     AS AMOUNT_DEP_FIXED    -- 定期存款余额
           ,COALESCE(FSE.WSUM_AMOUNT,0)                                                           AS WAMOUNT_LOAN        -- 贷款余额加权值
           ,CASE WHEN TERM.LOAN_TERM_TYPE_ID='A' THEN COALESCE(FSE.WSUM_AMOUNT,0) ELSE 0 END      AS WAMOUNT_LOAN_SHORT  -- 短期贷款余额加权值
           ,CASE WHEN TERM.LOAN_TERM_TYPE_ID='B' THEN COALESCE(FSE.WSUM_AMOUNT,0) ELSE 0 END      AS WAMOUNT_LOAN_LONG   -- 中长期贷款余额加权值
           ,0                                                                                     AS WAMOUNT_DEP         -- 存款总余额加权值
           ,0                                                                                     AS WAMOUNT_DEP_CURR    -- 活期存款余额加权值
           ,0                                                                                     AS WAMOUNT_DEP_FIXED   -- 定期存款余额加权值
     FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DATA_DATE='20180331') FSE -- 贷款发生额
     LEFT JOIN DIMENSION.DIM_LOAN_TERM_TYPE TERM ON FSE.TERM_TYPE=TERM.LOAN_TERM_TYPE_ID AND '20180331' BETWEEN TERM.START_DATE AND TERM.END_DATE AND TERM.IS_VALID='1'

     UNION ALL

     SELECT DEPF.DATA_DATE                                                                                  AS DATA_DATE
           ,DEPF.FIN_ORG_NO                                                                                 AS FIN_ORG_NO
           ,DEPF.CCY                                                                                        AS CCY
           ,0                                                                                               AS BAL_LOAN            -- 贷款总发生额
           ,0                                                                                               AS BAL_LOAN_SHORT      -- 短期贷款发生额
           ,0                                                                                               AS BAL_LOAN_LONG       -- 中长期贷款发生额
           ,COALESCE(DEPF.ACTUAL_AMOUNT,0)                                                                  AS BAL_DEP             -- 存款总发生额
           ,CASE WHEN DEPF.DEPOSIT_TYPE IN('D011','D013') THEN COALESCE(DEPF.ACTUAL_AMOUNT,0) ELSE 0 END    AS BAL_DEP_CURR        -- 活期存款发生额
           ,CASE WHEN DEPF.DEPOSIT_TYPE IN('D012','D014') THEN COALESCE(DEPF.ACTUAL_AMOUNT,0) ELSE 0 END    AS BAL_DEP_FIXED       -- 定期存款发生额
           ,0                                                                                               AS WAMOUNT_LOAN        -- 贷款发生额加权值
           ,0                                                                                               AS WAMOUNT_LOAN_SHORT  -- 短期贷款发生额加权值
           ,0                                                                                               AS WAMOUNT_LOAN_LONG   -- 中长期贷款发生额加权值
           ,COALESCE(DEPF.WSUM_AMOUNT,0)                                                                    AS WAMOUNT_DEP         -- 存款总发生额加权值
           ,CASE WHEN DEPF.DEPOSIT_TYPE IN('D011','D013') THEN COALESCE(DEPF.WSUM_AMOUNT,0) ELSE 0 END      AS WAMOUNT_DEP_CURR    -- 活期存款发生额加权值
           ,CASE WHEN DEPF.DEPOSIT_TYPE IN('D012','D014') THEN COALESCE(DEPF.WSUM_AMOUNT,0) ELSE 0 END      AS WAMOUNT_DEP_FIXED   -- 定期存款发生额加权值
     FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='20180331'AND DEPOSIT_TYPE IN('D011','D012','D013','D014')) DEPF -- 存款发生额
     ) CL
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON CL.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
    GROUP BY  AREA.AREA_CODE_4
             ,CL.CCY
             ,CONCAT(SUBSTR(CL.DATA_DATE,1,4),'年',SUBSTR(CL.DATA_DATE,5,2),'月')
    ;